2.6 Redis Key Design Tips

This is a historical learning note and may contain outdated or incomplete understanding.

1. MySQL -> Redis

1.1. Single Table

Primary key column
set table-name:primary-key-name primary-key-value
Other columns
set table-name:primary-key-name:primary-key-value:column-name column-value

1.1.1. User Table

Query a record by primary key

MySQL
User table:

userid	username	password	email
9	Lisi	1111111	[redacted email]

select * from user where userid=9;

Redis

set  user:userid  9
set  user:userid:9:username lisi
set  user:userid:9:password 111111
set  user:userid:9:email   [redacted email]

keys user:userid:9*
# Output
1) "user:userid:9:password"
2) "user:userid:9:username"
3) "user:userid:9:email"

Query a record by a non-primary-key column
Redundancy.

For example, in MySQL we can query by username:

select * from user where username='lisi';

Then in Redis we need to record a username->uid mapping:

set  user:username:lisi:uid  9

In this way, we can use get username:lisi:uid to find userid=9, and then query user:9:password/email …

1.2. Multiple Tables

One
set table-name:primary-key-name:primary-key-value:column-name column-value
Many
sadd table-name:column-name:column-value foreign-key-value
hset table-name:primary-key-name:primary-key-value column-name1:column-value1 column-name2:column-value2

1.2.1. Book Tags

One book has multiple tags, and one tag belongs to multiple books.

MySQL
Book table:

bookid	title
5	PHP Bible
6	Ruby in Practice
7	MySQL Operations
8	Ruby Server Programming

Tag table:

tid	bookid	content
10	5	PHP
11	5	WEB
12	6	WEB
13	6	RUBY
14	7	DATABSE
15	8	RUBY
16	8	SERVER

Query: books that have both PHP and WEB
select distinct bookid from tag where content = 'PHP' and content='WEB';

Query: books that have either PHP or WEB tags
select distinct bookid from tag where content in ('PHP', 'WEB');

Query: books that have the ruby tag but not the WEB tag
select distinct bookid from tag where content = 'ruby' and not exitst (select * from tag where content='WEB')

Redis

set book:bookid:5:title 'PHP Bible'
set book:bookid:6:title 'Ruby in Practice'
set book:bookid:7:title 'MySQL Operations'
set book:bookid:8:title 'ruby server'

sadd tag:PHP 5
sadd tag:WEB 5 6
sadd tag:database 7
sadd tag:ruby 6 8
sadd tag:SERVER 8

Query: books that have both PHP and WEB
Sinter tag:PHP tag:WEB  # Query the intersection of sets

Query: books that have either PHP or WEB tags
Sunin tag:PHP tag:WEB

Query: books that have the ruby tag but not the WEB tag
Sdiff tag:ruby tag:WEB # Set difference

1.2.2. User Red-Packet List

One live program (programId) corresponds to multiple red-packet tasks (taskId); one red-packet task (taskId) corresponds to multiple users (uid) who can claim it.

MySQL
Live-program table (program):

programId	xxx
1	yyy

Red-packet task table (task):

taskId	programId	xxx
2	1	yyy
3	1	yyy

User table (user):

uid	xxx
3	yyy

Table of red packets the user can claim (red_package):
The primary key is programId_taskId_uid.

uid	programId	taskId	status
3	1	2	0
3	1	3	0

Query the red-packet list a user can claim for a program:

select taskId,status from red_package where programId=1 and uid = 3;

Redis

# String usage 1: later values overwrite earlier ones
set  red_package:programId_uid:1_3:taskId 2
set  red_package:programId_uid:1_3:status 0
set  red_package:programId_uid:1_3:taskId 3
set  red_package:programId_uid:1_3:status 0

# String usage 2: a timeout can be set separately for this column
set  red_package:programId_taskId_uid:1_2_3:status 0
set  red_package:programId_taskId_uid:1_3_3:status 0
# Query the red-packet list a user can claim for a program
keys red_package:programId_taskId_uid:1_*_3:status
1) "red_package:programId_taskId_uid:1_2_3:status"
2) "red_package:programId_taskId_uid:1_3_3:status"

# hset usage: a timeout can only be set for the whole key
hset  red_package:programId_uid:1_3 taskId:2 status:0
hset  red_package:programId_uid:1_3 taskId:3 status:0
# Query the red-packet list a user can claim for a program
hgetall red_package:programId_uid:1_3
1) "taskId:2"
2) "status:0"
3) "taskId:3"
4) "status:0"

1.3. Weibo

MySQL
User table (user):

userid	username	password	email
9	Lisi	1111111	[redacted email]
8	zhangsan	3333333	[redacted email]

Post table (post):

postid	userid	username	time	content
1	9	Lisi	1596338654824	test

Follower table (follower):

userid	followerid
9	8

Push table (push):

userid	postid	time
8	1	1596338654824

# People I follow
select distinct userid from follower where followerid = 9;

# People who follow me
select distinct followerid from follower where userid = 9;

# Posts pushed to me
select postid from push where userid=9 order by time desc;

Redis

set  user:postid  1
set  user:postid:1:username lisi
set  user:postid:1:password 111111
set  user:postid:1:email   [redacted email]

set  post:userid  9
set  post:userid:9:userid 9
set  post:userid:9:username Lisi
set  post:userid:9:time   1596338654824
set  post:userid:9:content   test

# 2. People who follow me
sadd follower:userid:9 8
# 3. People I follow
sadd follower:followerid:8 9
# Posts pushed to me
rpush push:userid:8 1

2. string vs hash

If the stored data is relatively structured, such as cached user data, or if one or several fields need to be operated on frequently—especially when an object has many fields but only one or a few are needed each time—using a hash is a good choice, because it provides hget and hmget without requiring all data to be fetched and then processed in code.

Conversely, if the data varies greatly and operations often need to read all of it before processing, using a string is a good choice.

If a hash has a large number of fields (thousands or tens of thousands), consider whether splitting it into strings for storage would be a better choice.